﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlServerCe;
using Mimoza.Common;

namespace Mimoza.DBLayer.SQLSC
{
    public class SQLCS_DBContainer: Mimoza.Common.IDBContainerRepository
    {
        private SqlCeConnection connection = null;

        private SQLCS_DBContainer()
        { }

        public SQLCS_DBContainer(SqlCeConnection conn)
        {
            connection = conn;
        }

       #region IDBContainer Members

        Mimoza.Common.ContainerRecord Mimoza.Common.IDBContainerRepository.GetContainer(Guid ID)
        {
            SqlCeCommand cmd = null;

            try
            {
                cmd = connection.CreateCommand();
                cmd.CommandText = SQLCS_Helper.SelectContainersString + " WHERE [ID] = @ID";
                cmd.Parameters.AddWithValue("@ID", ID);

                SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
                string[] columns = rs.ResultSetView.Columns;

                if (!rs.ReadFirst()) return null;

                return SQLCS_Helper.ContainerRecordFromResultSet(rs, columns);
            }
            finally
            {
                if (null != cmd) cmd.Dispose();
            }           
        }

        IEnumerable<Mimoza.Common.ContainerRecord> Mimoza.Common.IDBContainerRepository.GetChildsContainer(Guid containerID)
        {          
            SqlCeCommand cmd = null;

            try
            {
                bool bIsParentIdNull = containerID.Equals(new Guid());

                cmd = connection.CreateCommand();
                cmd.CommandText = SQLCS_Helper.SelectContainersString;
                if (bIsParentIdNull)
                {
                    cmd.CommandText += " WHERE [ParentID] IS NULL";
                }
                else
                {
                    cmd.CommandText += " WHERE [ParentID] = @ParentID ORDER BY ID";
                    cmd.Parameters.AddWithValue("@ParentID", containerID);
                }

                SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
                string[] columns = rs.ResultSetView.Columns;

                if (!rs.ReadFirst()) yield break;

                do
                {
                    yield return SQLCS_Helper.ContainerRecordFromResultSet(rs, columns);
                } while (rs.Read());
            }
            finally
            {
                if (null != cmd) cmd.Dispose();
            }           
        }

        IEnumerable<Mimoza.Common.HostRecord> Mimoza.Common.IDBContainerRepository.GetHosts(Guid containerID)
        {
            SqlCeCommand cmd = null;

            try
            {
                bool bFillCondition = (containerID == Guid.Empty)?false:true;

                cmd = connection.CreateCommand();
                cmd.CommandText = SQLCS_Helper.SelectHostsString;

                if (bFillCondition)
                {
                    cmd.CommandText += " WHERE [ParentID] = @ParentID";
                    cmd.Parameters.AddWithValue("@ParentID", containerID);
                }

                SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
                
                string[] columns = rs.ResultSetView.Columns;

                if (!rs.ReadFirst()) yield break;

                do
                {
                    HostRecord record = new HostRecord();
                    SQLCS_Helper.RecordFromResultSet(record, rs, columns);

                    yield return record;

                    //yield return SQLCS_Helper.HostRecordFromResultSet(rs);
                } while (rs.Read());
            }
            finally
            {
                if (null != cmd) cmd.Dispose();
            }           
        }

        IEnumerable<Mimoza.Common.PolicyRecord> Mimoza.Common.IDBContainerRepository.GetPolicies(Guid containerID)
        {
            SqlCeCommand cmd = null;

            try
            {
                cmd = connection.CreateCommand();
                cmd.CommandText = SQLCS_Helper.SelectPolicyString + " WHERE [ParentID] = @ParentID";
                cmd.Parameters.AddWithValue("@ParentID", containerID);

                SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);

                string[] columns = rs.ResultSetView.Columns;

                if (!rs.ReadFirst()) yield break;

                do
                {
                    yield return SQLCS_Helper.PolicyRecordFromResultSet(rs, columns);
                } while (rs.Read());
            }
            finally
            {
                if (null != cmd) cmd.Dispose();
            }           
        }

        void Mimoza.Common.IDBContainerRepository.Insert(Mimoza.Common.ContainerRecord container)
        {
            SqlCeCommand cmd = null;

            try
            {
                if (container.ID == null || container.ID == Guid.Empty)
                {
                    container.ID = Guid.NewGuid();
                }

                container.IsSystem = false;
                container.UpdateDate = DateTime.Now;

                cmd = connection.CreateCommand();

                SQLCS_Helper.GreateInsertCommand(cmd, "Containers", container);

                cmd.ExecuteNonQuery();

                RaiseOnObjectChanged(new DBObjectChangedEventArgs(container, DBEventType.ObjectInserted));
            }
            finally
            {
                if (null != cmd) cmd.Dispose();
            }           
        }

        void Mimoza.Common.IDBContainerRepository.Update(Mimoza.Common.ContainerRecord container)
        {
            SqlCeCommand cmd = null;

            try
            {
                container.IsSystem = false;
                container.UpdateDate = DateTime.Now;

                cmd = connection.CreateCommand();

                SQLCS_Helper.GreateUpdateCommand(cmd, "Containers", container);

                cmd.ExecuteNonQuery();

                RaiseOnObjectChanged(new DBObjectChangedEventArgs(container, DBEventType.ObjectUpdated));
            }
            finally
            {
                if (null != cmd) cmd.Dispose();
            }           
        }

        void Mimoza.Common.IDBContainerRepository.Delete(Mimoza.Common.ContainerRecord container)
        {
            SqlCeCommand cmd = null;

            try
            {
                cmd = connection.CreateCommand();
                cmd.CommandText = "DELETE FROM Containers WHERE [ID] = @ID";
                cmd.Parameters.AddWithValue("@ID", container.ID);

                cmd.ExecuteNonQuery();

                RaiseOnObjectChanged(new DBObjectChangedEventArgs(container, DBEventType.ObjectDeleted));
            }
            finally
            {
                if (null != cmd) cmd.Dispose();
            }           
        }

        public event EventHandler<DBObjectChangedEventArgs> OnObjectChanged;

        private void RaiseOnObjectChanged(DBObjectChangedEventArgs e)
        {
            EventHandler<DBObjectChangedEventArgs> handler = OnObjectChanged;

            if (handler != null)
            {
                handler(this, e);
            }
        }
       #endregion
    }
}
